﻿using Microsoft.VisualBasic;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSharp.Study.IOTest
{
    public class ExcelHelper
    {
        /// <summary>
        /// 读取Excel到DataSet
        /// </summary>
        public static DataSet? ReadExcelToDataSet(string filePath, string? sheetName = null)
        {
            if (!File.Exists(filePath)) return null;
            using FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            return ReadExcelToDataSet(fs, sheetName);
        }

        /// <summary>
        /// 读取Excel到DataSet
        /// </summary>
        public static DataSet? ReadExcelToDataSet(FileStream fs, string? sheetName = null)
        {
            try
            {
                using IWorkbook workbook = WorkbookFactory.Create(fs);
                //获取sheet信息
                ISheet? sheet = null;
                DataSet ds = new DataSet();
                if (!string.IsNullOrWhiteSpace(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) return null;
                    DataTable? dt = ReadSheetToDataTable(sheet);
                    if (dt != null)
                        ds.Tables.Add(dt);
                }
                else
                {
                    //遍历获取所有数据
                    int sheetCount = workbook.NumberOfSheets;
                    for (int i = 0; i < sheetCount; i++)
                    {
                        sheet = workbook.GetSheetAt(i);
                        if (sheet != null)
                        {
                            DataTable? dt = ReadSheetToDataTable(sheet);
                            if (dt != null)
                                ds.Tables.Add(dt);
                        }
                    }
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("表格结构异常：" + ex.Message);
            }
        }

        /// <summary>
        /// 读取Sheet信息到DataTable
        /// </summary>
        private static DataTable? ReadSheetToDataTable(ISheet sheet)
        {
            DataTable dt = new DataTable();
            //获取列信息
            IRow row = sheet.GetRow(sheet.FirstRowNum);
            //空数据行返回
            if (row == null) return null;
            int cellsCount = row.Cells.Count;
            //空列返回
            if (cellsCount == 0) return null;
            int emptyCount = 0;
            int titleRowIndex = sheet.FirstRowNum;
            List<string> listColumns = new List<string>();
            while (true)
            {
                emptyCount = 0;
                listColumns.Clear();
                for (int i = 0; i < cellsCount; i++)
                {
                    if (row.GetCell(i) == null)
                        emptyCount++;
                    else
                    {
                        //这里可以判断数据类型
                        string title = row.GetCell(i).CellType switch
                        {
                            CellType.Numeric => row.GetCell(i).NumericCellValue.ToString(),
                            _ => row.GetCell(i).StringCellValue
                        };
                        listColumns.Add(title);
                    }
                }
                //这里根据逻辑需要，空列超过多少判断
                if (emptyCount == 0) break;
                if (titleRowIndex++ > 5)
                    throw new Exception("找不到标题行！");
                row = sheet.GetRow(titleRowIndex);
                while (row == null)
                {
                    row = sheet.GetRow(++titleRowIndex);
                    if (titleRowIndex > 5)
                        throw new Exception("找不到标题行！");
                }
            }

            foreach (string columnName in listColumns)
            {
                if (dt.Columns.Contains(columnName))//跳过重复列名
                    continue;
                dt.Columns.Add(columnName, typeof(string));
            }
            DataRow dr = null;
            //titleRowIndex + 1  (标题行索引 + 1 = 数据行索引)  遍历数据行获取数据
            for (int i = titleRowIndex + 1; i <= sheet.LastRowNum; i++)
            {
                row = sheet.GetRow(i);
                dr = dt.NewRow();
                if (row == null)
                {
                    dt.Rows.Add(dr);
                    continue;
                }
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (row.GetCell(j) == null)
                    {
                        dr[j] = null;
                        continue;
                    }
                    //这里可以判断数据类型
                    switch (row.GetCell(j).CellType)
                    {
                        case CellType.String:
                            dr[j] = row.GetCell(j).StringCellValue;
                            break;
                        case CellType.Numeric:
                            dr[j] = row.GetCell(j).NumericCellValue.ToString();
                            break;
                        case CellType.Unknown:
                            dr[j] = row.GetCell(j).StringCellValue;
                            break;
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }
    }
}
